﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Ledict.Core.Data;
using System.Data.Common;
using Ledict.ConditionExtract.Model;
using Ledict.ConditionExtract.Common;

namespace Ledict.ConditionExtract.DAL
{
    /// <summary>
    /// 引用法规DAL
    /// </summary>
    public class QuoteLawRelDAL
    {
        private string DbName, DbName2;
        public QuoteLawRelDAL()
        {
            DbName = "JudgeDoc";
            DbName2 = "JudgeProcess";
        }



        public bool Add(SYS_QuoteLawRel Model)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("insert into SYS_QuoteLawRel(");
                strSql.Append("QLR_ID,OLR_CaseCauseID,OLR_SituationTypeID,OLR_SituationID,OLR_LevelID,OLR_LawID,OLR_LawName,OLR_Law_ProvID,OLR_Law_ProvName,OLR_Law_ClauseNumber,OLR_Law_ClauseName,OLR_Law_ItemNumber,OLR_Law_ItemName,OLR_Law_HWType,OLR_Law_HWName,OLR_Law_PromuDate,OLR_Law_LostFlag,OLR_Law_ProvNumber,OLR_LawFlag,OLR_LawEffectiveDate,OLR_LawLibraryID,QLR_IsBasicLaw,QLR_ProvType)");
                strSql.Append(" values (");
                strSql.Append("@QLR_ID,@OLR_CaseCauseID,@OLR_SituationTypeID,@OLR_SituationID,@OLR_LevelID,@OLR_LawID,@OLR_LawName,@OLR_Law_ProvID,@OLR_Law_ProvName,@OLR_Law_ClauseNumber,@OLR_Law_ClauseName,@OLR_Law_ItemNumber,@OLR_Law_ItemName,@OLR_Law_HWType,@OLR_Law_HWName,@OLR_Law_PromuDate,@OLR_Law_LostFlag,@OLR_Law_ProvNumber,@OLR_LawFlag,@OLR_LawEffectiveDate,@OLR_LawLibraryID,@QLR_IsBasicLaw,@QLR_ProvType)");
                Database oBase = DatabaseFactory.CreateDatabase(DbName);
                DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());

                oBase.AddInParameter(oCmd, "@QLR_ID", DbType.String, Model.QLR_ID);
                oBase.AddInParameter(oCmd, "@OLR_CaseCauseID", DbType.String, Model.OLR_CaseCauseID);
                oBase.AddInParameter(oCmd, "@OLR_SituationTypeID", DbType.Int32, Model.OLR_SituationTypeID);
                oBase.AddInParameter(oCmd, "@OLR_SituationID", DbType.String, Model.OLR_SituationID);
                oBase.AddInParameter(oCmd, "@OLR_LevelID", DbType.Int32, Model.OLR_LevelID);
                oBase.AddInParameter(oCmd, "@OLR_LawID", DbType.String, Model.OLR_LawID);
                oBase.AddInParameter(oCmd, "@OLR_LawName", DbType.String, Model.OLR_LawName);
                oBase.AddInParameter(oCmd, "@OLR_Law_ProvID", DbType.String, Model.OLR_Law_ProvID);
                oBase.AddInParameter(oCmd, "@OLR_Law_ProvName", DbType.String, Model.OLR_Law_ProvName);
                oBase.AddInParameter(oCmd, "@OLR_Law_ClauseNumber", DbType.String, Model.OLR_Law_ClauseNumber);
                oBase.AddInParameter(oCmd, "@OLR_Law_ClauseName", DbType.String, Model.OLR_Law_ClauseName);
                oBase.AddInParameter(oCmd, "@OLR_Law_ItemNumber", DbType.String, Model.OLR_Law_ItemNumber);
                oBase.AddInParameter(oCmd, "@OLR_Law_ItemName", DbType.String, Model.OLR_Law_ItemName);
                oBase.AddInParameter(oCmd, "@OLR_Law_HWType", DbType.String, Model.OLR_Law_HWType);
                oBase.AddInParameter(oCmd, "@OLR_Law_HWName", DbType.String, Model.OLR_Law_HWName);
                oBase.AddInParameter(oCmd, "@OLR_Law_PromuDate", DbType.DateTime, Model.OLR_Law_PromuDate);
                oBase.AddInParameter(oCmd, "@OLR_Law_LostFlag", DbType.String, Model.OLR_Law_LostFlag);
                oBase.AddInParameter(oCmd, "@OLR_Law_ProvNumber", DbType.String, Model.OLR_Law_ProvNumber);
                oBase.AddInParameter(oCmd, "@OLR_LawFlag", DbType.Int32, Model.OLR_LawFlag);
                oBase.AddInParameter(oCmd, "@OLR_LawEffectiveDate", DbType.String, Model.OLR_LawEffectiveDate.ToString("yyyy-MM-dd"));
                oBase.AddInParameter(oCmd, "@OLR_LawLibraryID", DbType.String, Model.HCode);
                oBase.AddInParameter(oCmd, "@QLR_IsBasicLaw", DbType.Int32, Model.QLR_IsBasicLaw);
                oBase.AddInParameter(oCmd, "@QLR_ProvType", DbType.String, Model.QLR_ProvType);

                if (oBase.ExecuteNonQuery(oCmd) > 0)
                {
                    return true;
                }
                return false;
            }
            catch (Exception error)//唯一键
            {
                error.ToString();
                return false;
            }
        }

        /// <summary>
        /// 插入引用法规主题词关联法条表
        /// </summary>
        /// <param name="Model"></param>
        /// <returns></returns>
        public bool AddQuoteLawSet(QuoteLawSet Model)
        {
            try
            {
                string sql = string.Format(" insert into QuoteLawSet values('{0}',{1},{2}) ", Model.ProvHwordInID, Model.checkright, Model.checkwrong);
                Database oBase = DatabaseFactory.CreateDatabase(DbName2);
                DbCommand oCmd = oBase.GetSqlStringCommand(sql);
                if (oBase.ExecuteNonQuery(oCmd) > 0)
                {
                    return true;
                }
                return false;
            }
            catch (Exception error)//唯一键
            {
                error.ToString();
                return false;
            }
        }

        /// <summary>
        ///  修改引用法规主题词关联法条表
        /// </summary>
        /// <param name="Model"></param>
        /// <returns></returns>
        public bool UpdateQuoteLawSet(QuoteLawSet Model)
        {
            try
            {
                string sql = string.Format(" update QuoteLawSet set checkright={0},checkwrong={1} where ProvHwordInID= {2} ", Model.checkright, Model.checkwrong, Model.ProvHwordInID);
                Database oBase = DatabaseFactory.CreateDatabase(DbName2);
                DbCommand oCmd = oBase.GetSqlStringCommand(sql);
                if (oBase.ExecuteNonQuery(oCmd) > 0)
                {
                    return true;
                }
                return false;
            }
            catch (Exception error)//唯一键
            {
                error.ToString();
                return false;
            }
        }

        /// <summary>
        /// 判断引用法规主题词关联法条表是否已存在记录
        /// </summary>
        /// <param name="ProvID"></param>
        /// <returns></returns>
        public bool IsExistsQuoteLawSet(string ProvHwordInID)
        {
            string sql = string.Format(@" select count(1) from QuoteLawSet where ProvHwordInID = '" + ProvHwordInID + "'  ");
            Database oBase = DatabaseFactory.CreateDatabase(DbName2);
            DbCommand oCmd = oBase.GetSqlStringCommand(sql);
            return Convert.ToInt32(oBase.ExecuteScalar(oCmd)) > 0 ? true : false;
        }


        /// <summary>
        /// 获得数据列表(组合)
        /// </summary>
        /// <param name="caseTypeId"></param>
        /// <returns></returns>
        public DataTable GetQuoteLawSet()
        {
            try
            {
                string strSql = " SELECT * from QuoteLawSet ";
                Database oBase = DatabaseFactory.CreateDatabase(DbName2);
                DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
                DataTable otB = oBase.ExecuteDataSet(oCmd).Tables[0];
                if (otB.Rows.Count > 0)
                {
                    return otB;
                }
                else
                {
                    return new DataTable();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }


        public string isAdd(string ProvInID, string SituationID)//where OLR_SituationID=@SituationID 
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append(@" select Count(*) from SYS_QuoteLawRel where OLR_SituationID=@SituationID and OLR_Law_ProvID=@ProvInID
	        ");

            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            oBase.AddInParameter(oCmd, "@SituationID", DbType.String, SituationID);
            oBase.AddInParameter(oCmd, "@ProvInID", DbType.String, ProvInID);
            return LdFormat.ObjectToString(oBase.ExecuteScalar(oCmd));

        }

        /// <summary>
        /// 修改数据
        /// </summary>
        /// <param name="Model"></param>
        /// <returns></returns>
        public bool Update(SYS_QuoteLawRel Model)
        {
            try
            {
                string sql = string.Format(@" update SYS_QuoteLawRel set OLR_Law_ClauseNumber='{0}',OLR_Law_ClauseName='{1}',
                    OLR_Law_ItemNumber='{2}',OLR_Law_ItemName='{3}' where QLR_ID='{4}' ",
                    Model.OLR_Law_ClauseNumber, Model.OLR_Law_ClauseName, Model.OLR_Law_ItemNumber, Model.OLR_Law_ItemName, Model.QLR_ID);
                Database oBase = DatabaseFactory.CreateDatabase(DbName);
                DbCommand oCmd = oBase.GetSqlStringCommand(sql.ToString());
                if (oBase.ExecuteNonQuery(oCmd) > 0)
                {
                    return true;
                }
                return false;
            }
            catch (Exception error)//唯一键
            {
                error.ToString();
                return false;
            }
        }


        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(string QLR_ID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from SYS_QuoteLawRel ");
            strSql.Append(" where QLR_ID=@QLR_ID ");

            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());

            oBase.AddInParameter(oCmd, "@QLR_ID", DbType.String, QLR_ID);

            if (oBase.ExecuteNonQuery(oCmd) > 0)
            {
                return true;
            }
            return false;
        }


        /// <summary>
        /// 获得数据列表(刑事)
        /// </summary>
        public DataTable QuoteLawRelGetList(string situationId)//where OLR_SituationID=@SituationID 
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select  row_number()over ( order by QLR_ID ) as showindex  ,* 
            from dbo.SYS_QuoteLawRel 
	        where OLR_SituationID=@SituationID");

            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            oBase.AddInParameter(oCmd, "@SituationID", DbType.String, situationId);


            DataTable otB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (otB.Rows.Count > 0)
            {
                return otB;
            }
            else
            {
                return new DataTable();
            }
        }

        /// <summary>
        /// 获得数据列表(程序法)
        /// </summary>
        /// <param name="caseTypeId"></param>
        /// <returns></returns>
        public DataTable QuoteLawRelByProceduralLaw(string caseTypeId)
        {
            try
            {
                string strSql = string.Format(@" SELECT
	                                                    row_number () OVER (ORDER BY QLR_ID) AS showindex ,*
                                                    FROM
	                                                    dbo.SYS_QuoteLawRel
                                                    INNER JOIN SYS_QuoteLawCondition ON OLR_SituationID = QLC_ID
                                                    WHERE
	                                                    [QLC_CaseType] ='{0}' ", caseTypeId);
                Database oBase = DatabaseFactory.CreateDatabase(DbName);
                DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
                DataTable otB = oBase.ExecuteDataSet(oCmd).Tables[0];
                if (otB.Rows.Count > 0)
                {
                    return otB;
                }
                else
                {
                    return new DataTable();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 获得数据列表(组合)
        /// </summary>
        /// <param name="caseTypeId"></param>
        /// <returns></returns>
        public DataTable QuoteLawRelByCombiationLaw(string caseTypeId)
        {
            try
            {
                string strSql = string.Format(@" SELECT
	                                                    row_number () OVER (ORDER BY QLR_ID) AS showindex ,QLR_ID,OLR_SituationID,OLR_LawID,OLR_LawName,OLR_Law_ProvID,OLR_Law_ProvName,
														OLR_Law_ClauseNumber,OLR_Law_ClauseName,OLR_Law_ItemNumber,OLR_Law_ItemName,OLR_Law_HWType,OLR_Law_HWName,OLR_Law_PromuDate,OLR_Law_LostFlag,
														QLC_Name,QLC_Content,QLC_Explain,QLC_ID
                                                    FROM
	                                                    dbo.SYS_QuoteLawRel
                                                    INNER JOIN SYS_QuoteLawCondition ON OLR_SituationID = QLC_ID
                                                    WHERE
	                                                    [QLC_CaseCauseId] ='{0}' ", caseTypeId);
                Database oBase = DatabaseFactory.CreateDatabase(DbName);
                DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
                DataTable otB = oBase.ExecuteDataSet(oCmd).Tables[0];
                if (otB.Rows.Count > 0)
                {
                    return otB;
                }
                else
                {
                    return new DataTable();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }


        /// <summary>
        /// 获得数据列表(民事)
        /// </summary>
        public DataTable QuoteLawRelGetListByMinShi(string situationId)//where OLR_SituationID=@SituationID 
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select  row_number()over ( order by QLR_ID ) as showindex  ,*  from dbo.SYS_QuoteLawRel join SYS_Label_LabelRel on OLR_CaseCauseID=LLR_CaseCauseID
	        where OLR_CaseCauseID=@SituationID and LLR_LawLabelID=OLR_SituationID ");

            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            oBase.AddInParameter(oCmd, "@SituationID", DbType.String, situationId);


            DataTable otB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (otB.Rows.Count > 0)
            {
                return otB;
            }
            else
            {
                return new DataTable();
            }
        }

        public string GetHeadwordInID(string ccId)
        {
            string sql = $" SELECT HeadwordInID FROM [Ldt_JudgeDocSystem].[dbo].[SYS_CaseCause_HeadwordRel] where CC_ID='{ccId}' ";
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(sql);
            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB != null && OtB.Rows.Count > 0)
            {
                StringBuilder sb = new StringBuilder();
                foreach (DataRow dr in OtB.Rows)
                {
                    sb.Append("'" + dr[0].ToString() + "',");
                }
                return sb.ToString().Substring(0, sb.Length - 1);
            }
            return "";
        }

        public string QuoteLawRelGetProv(string SituationID)//where OLR_SituationID=@SituationID 
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"  declare @OLR_Law_ProvID varchar(max)
          set @OLR_Law_ProvID=''
           select  @OLR_Law_ProvID=@OLR_Law_ProvID+',' +''''+OLR_Law_ProvID +''''
            from dbo.SYS_QuoteLawRel 
            where isnull(OLR_Law_ProvID ,'')<>'' and OLR_SituationID=@SituationID
            select substring (@OLR_Law_ProvID,2,len(@OLR_Law_ProvID))
	        ");

            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            oBase.AddInParameter(oCmd, "@SituationID", DbType.String, SituationID);
            return LdFormat.ObjectToString(oBase.ExecuteScalar(oCmd));

        }

        /// <summary>
        /// 获取法规效力等级编号和名称
        /// </summary>
        /// <returns></returns>
        public List<SYS_EffectiveGrade> GetHeadWindows()
        {
            try
            {
                //sql语句
                //                SELECT a.[E_ID] as1, case when a.[E_Code] = 'G0106' then a.[E_Name] +'（法律）' else case when a.[E_Code] = 'G0204' then a.[E_Name] +'（行政法规）' else a.[E_Name] end end as2
                //FROM[SYS_EffectiveGrade] a
                //WHERE(((a.[E_ID]) in (SELECT DISTINCT a.[EffectiveGrade_ID]
                //    FROM[T_Law] a))) 
                //ORDER BY case when a.[eg_sort]  IS NULL then 1 else 0 end, a.[eg_sort]
                List<SYS_EffectiveGrade> lis = SqlManage.sqlServerLawMachine.Select<SYS_EffectiveGrade>().Where(a => SqlManage.sqlServerLawMachine.Select<T_Law>().Distinct().ToList(x => x.EffectiveGrade_ID).Contains(a.E_ID)).OrderBy(a => a.Eg_sort == null ? 1 : 0).OrderBy(a => a.Eg_sort).ToList(a => new SYS_EffectiveGrade { E_ID = a.E_ID, E_Name = (a.E_Code == "G0106" ? a.E_Name + "（法律）" : a.E_Code == "G0204" ? a.E_Name + "（行政法规）" : a.E_Name) });
                return lis;
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show("获取法规效力等级编号和名称出错:" + ex.Message);
                return new List<SYS_EffectiveGrade>();
            }

            #region 原有改版前代码
            //StringBuilder strSql = new StringBuilder();
            //// strSql.Append(@"  select 1, '全部'as HWName ,'' as HWType 
            ////     union 
            ////select 2, '法律' as HWName  ,  '''21'',''2A'''as HWType
            ////union 
            ////select 3,'行政法规' as HWName  ,  '''22'',''2B'''as HWType
            ////union 
            ////select 4,'部门规章' as HWName  ,  '''23'',''2C'''as HWType
            ////union 
            ////select 5,'司法解释' as HWName  ,  '''3'',''3B'''as HWType
            ////union 
            //// select 6,'地方法规' as HWName  ,  '''92'',''9B'''as HWType
            //// union 
            //// select 7,'地方规章' as HWName  ,  '''93'',''9C'''as HWType
            //// union 
            //// select 8,'国规文' as HWName  ,  '''2D'''as HWType
            //// union 
            //// select 9, '地规文' as HWName  ,  '''9D'''as HWType
            //// union 
            //// select 10,'地方司法性文件' as HWName  ,  '''9E'''as HWType

            ////  ");
            //strSql.Append(@" select HWType,HWName from HeadWindows ");

            //Database oBase = DatabaseFactory.CreateDatabase("lawwork");
            //DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());

            //DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            //if (OtB.Rows.Count > 0)
            //{
            //    return OtB;
            //}
            //else
            //{
            //    return new DataTable();
            //} 
            #endregion
        }

        public DataTable GetIDByName(string Name)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"  select    Id  from dbo.t_Case_HeadWords
            where HeadWordName=@Name
            ");
            Database oBase = DatabaseFactory.CreateDatabase("Caselibrary");
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            oBase.AddInParameter(oCmd, "@Name", DbType.String, Name);
            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }


        }

        /// <summary>
        /// 获得节点的条文ＩＤ
        /// </summary>
        public DataTable GetRelProvId(string SituationID, string SituationName, int isid)
        {

            StringBuilder strSql = new StringBuilder();
            if (isid == 1)
            {
                strSql.Append(@" select  row_number()over(order by Count(*) desc ) as showindex ,DataId as ProvInID,max(QuoteWord)as ProvInName ,-2 as  ProvNumber  from dbo.t_Case_QuoteItems   where CaseId in
                (
                select distinct  CaseId from dbo.t_Case_HeadWords_Associate 
                where  HeadWordId =@SituationID and ItemTypeId=2
                )
                group by DataId
             ");
            }
            else
            {
                strSql.Append(@"  select  row_number()over(order by ncount desc ) as showindex,* from (  
					select DataId as ProvInID,max(QuoteWord)as ProvInName,-2 as  ProvNumber,COUNT(*) as ncount     from dbo.t_Case_QuoteItems
		      where CaseId in
						(
						select  CaseId from dbo.t_Case_HeadWords_Associate 
						where  HeadWordId in (select    Id  from dbo.t_Case_HeadWords
					where HeadWordName=@SituationName)
						) and ItemTypeId=2    group by DataId
                )as abccc
                

             ");

            }
            Database oBase = DatabaseFactory.CreateDatabase("Caselibrary");
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            oBase.AddInParameter(oCmd, "@SituationID", DbType.String, SituationID);
            oBase.AddInParameter(oCmd, "@SituationName", DbType.String, SituationName);
            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }


        }
        /// <summary>
        /// 条文关键属性值
        /// </summary>
        /// <param name="ProvInID"></param>
        /// <returns></returns>
        public DataTable GetProvContent(string ProvInID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select  a.CATInID , CATName, PromuDate,b.HWType , HWName,LostFlag from dbo.LawProv a 
                join dbo.LawCAT b on a.CATInID=b.CATInID
                join dbo.HeadWindows as c 
                on b.HWType=c.HWType
                where ProvInID =  @ProvInID
                ");
            Database oBase = DatabaseFactory.CreateDatabase("lawwork");
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            oBase.AddInParameter(oCmd, "@ProvInID", DbType.String, ProvInID);

            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }

        }

        /// <summary>
        /// 获取法规添加法规信息(查询)
        /// </summary>
        /// <param name="condition">查询法律法规条件</param>
        /// <returns>返回法律法规查询用界面信息</returns>
        public DataTable GetQuoteData(NodeTreeCaseConditionData conditionData)
        {
            try
            {
                DataTable dt = SqlManage.sqlServerLawMachine.Select<T_Law, T_Law_Article, SYS_EffectiveGrade>()
                     .InnerJoin((a, b, c) => a.L_ID == b.L_ID)
                     .InnerJoin((a, b, c) => a.EffectiveGrade_ID == c.E_ID)
                     .WhereIf(conditionData.EIDs!=null&& conditionData.EIDs.Any(), (a, b, c) => conditionData.EIDs.Contains(Convert.ToInt32(a.EffectiveGrade_ID)))
                     .WhereIf(!string.IsNullOrEmpty(conditionData.LName), (a, b, c) => a.L_Name.Contains(conditionData.LName))
                     .WhereIf(conditionData.PromuDateStart != null, (a, b, c) => a.L_DateIssued >= conditionData.PromuDateStart)
                     .WhereIf(conditionData.PromuDateEnd != null, (a, b, c) => a.L_DateIssued <= conditionData.PromuDateEnd)
                     .WhereIf(!string.IsNullOrEmpty(conditionData.LpText), (a, b, c) => b.LP_Text.Contains(conditionData.LpText))
                     .Distinct()
                     .ToDataTable(@"a.L_ID as CATInID,  a.L_Name as CATName, a.L_Standardcatname as  Standardcatname,a.L_DateIssued as PromuDate,
               a.EffectiveStatus_ID as LostFlag, a.EffectiveGrade_ID as HWType, c.E_Name as HWName,  case a.EffectiveStatus_ID when '1' then 1 when '0' then 0 else 2 end as lawFlag,
               a.L_EffectiveDate as EffectiveDate,c.E_Code as HCode,a.IsBasicLaw, a.L_EnforceDate as EnforceDate ");
                return dt ?? new DataTable();
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show("获取法规添加法规信息(查询)出错：" + ex.Message);
                return new DataTable();
            }

            #region 原有改版前代码 
            // string sql = string.Empty;
            // sql = $@"  select distinct T_Law.L_ID as CATInID,  T_Law.L_Name as CATName, T_Law.L_Standardcatname as  Standardcatname,T_Law.L_DateIssued as PromuDate,
            //T_Law.EffectiveStatus_ID as LostFlag,T_Law.EffectiveGrade_ID as HWType,SYS_EffectiveGrade.E_Name as HWName,  case T_Law.EffectiveStatus_ID when '1' then 1 when '0' then 0 else 2 end as lawFlag,
            //T_Law.L_EffectiveDate as EffectiveDate,SYS_EffectiveGrade.E_Code as HCode,T_Law.IsBasicLaw, T_Law.L_EnforceDate as EnforceDate 
            //from T_Law as T_Law
            // join T_Law_Article as T_Law_Article on T_Law.L_ID=T_Law_Article.L_ID
            // join SYS_EffectiveGrade as SYS_EffectiveGrade on SYS_EffectiveGrade.E_ID=T_Law.EffectiveGrade_ID
            // where {condition} {provCondition52} ";
            // // sql += $@" union all select distinct T_Law.L_ID as CATInID,  T_Law.L_Name as CATName, T_Law.L_Standardcatname as  Standardcatname,T_Law.L_DateIssued as PromuDate,
            // //T_Law.EffectiveStatus_ID as LostFlag,T_Law.EffectiveGrade_ID as HWType,SYS_EffectiveGrade.E_Name as HWName,  case T_Law.EffectiveStatus_ID when '1' then 1 when '0' then 0 else 2 end as lawFlag,
            // //T_Law.L_EffectiveDate as EffectiveDate,SYS_EffectiveGrade.E_Code as HCode,T_Law.IsBasicLaw , T_Law.L_EnforceDate as EnforceDate
            // //from T_Law as T_Law
            // // join discretion_benchmark as discretion_benchmark on T_Law.L_ID=discretion_benchmark.CATInID
            // // join SYS_EffectiveGrade as SYS_EffectiveGrade on SYS_EffectiveGrade.E_ID=T_Law.EffectiveGrade_ID
            // //                 where {condition} {provCondition12}";
            // DataTable st = SqlManage.sqlServerLawMachine.Select<object>().WithSql(sql).ToDataTable();
            // return st ?? new DataTable();

            //           StringBuilder strSql = new StringBuilder();
            //           strSql.Append(@" select distinct b.CATInID,b.CATName,b.Standardcatname, b.PromuDate,b.LostFlag,b.HWType,c.HWName,case b.STFlag when '1' then 1 when '0' then 0 else 2 end as lawFlag ,EffectiveDate,e.HCode,d.IsBasicLaw   from dbo.LawCAT as b
            //	        inner join dbo.LawProv as a   on a.CATInID=b.CATInID
            //	        join dbo.HeadWindows as c    on c.HWType=b.HWType                  
            //	        left join   dbo.HeadWindows e on e.HWType=b.HWType    
            //	        left join  BasicLawRel d on a.CATInID=d.CATInID         
            //               where " + condition + @"
            //                              union all                 
            //select distinct b.CATInID,b.CATName,b.Standardcatname, b.PromuDate,b.LostFlag,b.HWType,c.HWName,case b.STFlag when '1' then 1 when '0' then 0 else 2 end as lawFlag ,EffectiveDate,e.HCode,d.IsBasicLaw   from dbo.LawCAT as b
            //	        inner join dbo.discretion_benchmark as a   on a.CATInID=b.CATInID
            //	        join dbo.HeadWindows as c    on c.HWType=b.HWType                  
            //	        left join   dbo.HeadWindows e on e.HWType=b.HWType    
            //	        left join  BasicLawRel d on a.CATInID=d.CATInID
            //               where " + condition + " ");
            //           Database oBase = DatabaseFactory.CreateDatabase("lawwork");
            //           DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            //           DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            //           if (OtB.Rows.Count > 0)
            //           {
            //               return OtB;
            //           }
            //           else
            //           {
            //               return new DataTable();
            //           } 
            #endregion
        }

        /// <summary>
        /// 获取法规界面信息(推送)
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public DataTable GetQuoteData1(string condition)
        {
            try
            {
                string sql = $@" select  T_Law.L_ID as CATInID,  T_Law.L_Name as CATName,T_Law.L_DateIssued as PromuDate, T_Law.EffectiveStatus_ID as LostFlag,
T_Law.EffectiveGrade_ID as HWType,SYS_EffectiveGrade.E_Name as HWName
 from T_Law as T_Law join SYS_EffectiveGrade as SYS_EffectiveGrade on SYS_EffectiveGrade.E_ID=T_Law.EffectiveGrade_ID
where  T_Law.L_ID in ({condition}) ";
                DataTable st = SqlManage.sqlServerLawMachine.Select<object>().WithSql(sql).ToDataTable();
                return st ?? new DataTable();
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show("获取法规界面信息(推送)出错：" + ex.Message);
                return new DataTable();
            }

            #region 原有代码
            //StringBuilder strSql = new StringBuilder();
            //strSql.Append(@" select  b.CATInID,b.CATName,b.PromuDate,b.LostFlag,b.HWType,c.HWName  from dbo.LawCAT b join dbo.HeadWindows c  on b.HWType=c.HWType where CATInID in(" + condition + ") ");
            //Database oBase = DatabaseFactory.CreateDatabase("lawwork");
            //DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            //DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            //if (OtB.Rows.Count > 0)
            //{
            //    return OtB;
            //}
            //else
            //{
            //    return new DataTable();
            //} 
            #endregion
        }

        // <summary>
        /// 获取法规信息(推送)
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public DataTable GetQuoteTuiSongData(string ccId)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append(@" select distinct DataId  from t_Case_QuoteItems where CaseId in(select caseid from t_Case_HeadWords_Associate where HeadWordId in('" + ccId + "')) and ItemTypeId = 1 ");
                Database oBase = DatabaseFactory.CreateDatabase("Caselibrary");
                DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
                DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
                if (OtB.Rows.Count > 0)
                {
                    return OtB;
                }
                else
                {
                    return new DataTable();
                }
            }
            catch (Exception ex)
            {
                //通过拉取数据的方法获取数据
                string strSql = string.Format(@" if not exists(select * from sysobjects where id=object_id('temp'))
	                                                begin
	                                                select  caseid,0 as flag into temp from t_Case_HeadWords_Associate where HeadWordId in('{0}')
                                                    select @@ROWCOUNT
	                                                end
                                                ELSE 
                                                begin
	                                                drop table  temp
	                                                select  caseid,0 as flag into temp from t_Case_HeadWords_Associate where HeadWordId in('{0}')
	                                                select @@ROWCOUNT
                                                end
                                                ", ccId);
                Database oBase = DatabaseFactory.CreateDatabase("Caselibrary");
                DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
                Int32 res = Convert.ToInt32(oBase.ExecuteScalar(oCmd).ToString());
                DataTable dt = new DataTable();
                if (res > 0)
                {
                    for (int i = 0; i < res; i = i + 1000)
                    {
                        string strSql1 = string.Format(@" 	select distinct DataId  from t_Case_QuoteItems where CaseId in(select top 1000 CaseId from temp where flag = 0);");
                        oCmd = oBase.GetSqlStringCommand(strSql1.ToString());
                        DataTable dt1 = oBase.ExecuteDataSet(oCmd).Tables[0];
                        dt.Merge(dt1);
                        string strSql2 = " update temp set flag = 1 where CaseId in(select top 1000 CaseId from temp where flag = 0)";
                        oCmd = oBase.GetSqlStringCommand(strSql2.ToString());
                        int j = Convert.ToInt32(oBase.ExecuteNonQuery(oCmd));
                    }
                    DataView dv = new DataView(dt);
                    return dv.ToTable(true, "DataId");
                }
                else
                {
                    return new DataTable();
                }
            }
        }

        /// <summary>
        /// 获取法律法规条信息（查询）
        /// </summary>
        /// <param name="CATInID"></param>
        /// <returns></returns>
        public List<T_Law_Article> GetLawProvData(string catInId, string provCondition52)
        {
            return SqlManage.sqlServerLawMachine.Select<T_Law_Article>().Where(x => x.LP_Type == "90" && x.L_ID == Convert.ToInt32(catInId)).WhereIf(!string.IsNullOrEmpty(provCondition52), x => x.LP_Text.Contains(provCondition52)).ToList();

            #region 原有代码
            //           string sql;
            //           sql = $@" select LP_ID as ProvInID,LP_Article_Number as ProvNumber, LP_Text as ProvText,LP_ArticleName as ProvInName,BasisType as BasisType from T_Law_Article
            //where LP_Type=90  and  L_ID  ='{catInId}' {provCondition52} ";

            //           //           sql += $@" union all  select BenchmarkID as ProvInID, BenchmarkNumber as ProvNumber,BenchmarkText as ProvText, BenchmarkName as ProvInName,'' as BasisType from discretion_benchmark
            //           //where CATInID ='{catInId}' {provCondition12} ";

            //           DataTable st = SqlManage.sqlServerLawMachine.Select<object>().WithSql(sql).ToDataTable();
            //           return st ?? new DataTable();
            //StringBuilder strSql = new StringBuilder();
            //strSql.Append($"  select a.ProvInID,ProvNumber,ProvText,ArticleName as ProvInName, b.BasisType from LawProv a  left join dbo.LawProvBasisType b on a.ProvInID = b.ProvInID where CATInID ='{ CATInID}'  and ProvNumber not in(-1,0)  ");
            //if (!string.IsNullOrEmpty(condition))
            //{
            //    //if (condition.Contains("ProvText"))
            //    //{
            //    //    condition = condition.Replace("ProvText", "ArticleName");
            //    //}
            //    strSql.Append(condition);
            //}
            //strSql.Append(@" order by ProvNumber ");

            //Database oBase = DatabaseFactory.CreateDatabase("lawwork");
            //DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            //DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            //if (OtB.Rows.Count > 0)
            //{
            //    return OtB;
            //}
            //else
            //{
            //    string Sql = $"   select ID as ProvInID,ID as ProvNumber,benchmark as ProvText,ID as ArticleName,b.BasisType  from dbo.discretion_benchmark a  left join dbo.LawProvBasisType b on a.ID = b.ProvInID where CATinID='{CATInID}'  ";
            //    Database oBase1 = DatabaseFactory.CreateDatabase("lawwork");
            //    DbCommand oCmd1 = oBase1.GetSqlStringCommand(Sql);
            //    DataTable OtB1 = oBase1.ExecuteDataSet(oCmd1).Tables[0];
            //    if (OtB1.Rows.Count > 0)
            //    {
            //        return OtB1;
            //    }
            //    else
            //    {
            //        return new DataTable();
            //    }
            //} 
            #endregion
        }

        /// <summary>
        /// 根据条ID获取下级是否有款或者项信息
        /// </summary>
        /// <param name="provId"></param>
        /// <returns></returns>
        public bool GetNumByProvId(int provId)
        {
            return SqlManage.sqlServerLawMachine.Select<T_Law_Article>().Where(x => x.LP_ParentID == provId).Any();
        }

        /// <summary>
        /// 获取法律法规款信息（查询）
        /// </summary>
        /// <param name="CATInID"></param>
        /// <returns></returns>
        public List<T_Law_Article> GetLawClauseData(int provInId)
        {
            //先根据条获取款信息
            List<T_Law_Article> listResult = SqlManage.sqlServerLawMachine.Select<T_Law_Article>().Where(x => x.LP_ParentID == provInId && x.LP_Type == "91").OrderBy(x => x.LP_Paragraph_Number).ToList();
            if (listResult != null && listResult.Count > 0)
            {
                return listResult;
            }
            else
            {
                //再根据条获取项信息
                listResult = SqlManage.sqlServerLawMachine.Select<T_Law_Article>().Where(x => x.LP_ParentID == provInId && x.LP_Type == "92").OrderBy(x => x.LP_Item_Number).ToList();
                if (listResult != null && listResult.Count > 0)
                {
                    return listResult;
                }
                else
                {
                    return new List<T_Law_Article>();
                }
            }
            #region 原有代码
            //StringBuilder strSql = new StringBuilder();
            //strSql.Append(@"   select ClauseNumber,ClauseInID,ClauseName as ClauseName, ClauseText from LawProvClauseItem where provinId  = '" + provInId + "' and itemNumber is null  ");
            //Database oBase = DatabaseFactory.CreateDatabase("lawwork");
            //DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            //DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            //if (OtB.Rows.Count > 0)
            //{
            //    return OtB;
            //}
            //else
            //{
            //    return new DataTable();
            //    //StringBuilder strSql1 = new StringBuilder();
            //    //strSql1.Append(@"   select  ClauseNumber,ClauseInID,ClauseName,ClauseText from LawProvClause where ProvInID='" + provInId + "'  ");
            //    //Database oBase1 = DatabaseFactory.CreateDatabase("lawwork");
            //    //DbCommand oCmd1 = oBase1.GetSqlStringCommand(strSql1.ToString());
            //    //DataTable OtB1 = oBase1.ExecuteDataSet(oCmd1).Tables[0];
            //    //if (OtB1.Rows.Count > 0)
            //    //{
            //    //    return OtB1;
            //    //}
            //    //else
            //    //{
            //    //    return new DataTable();
            //    //}
            //} 
            #endregion
        }

        #region 获取法律法规项信息（查询）
        /// <summary>
        /// 获取法律法规项信息（查询）
        /// </summary>
        /// <param name="CATInID"></param>
        /// <returns></returns>
        //public DataTable GetLawItemData(string ClauseNumber, string provInId)
        //{
        //    try
        //    {
        //        StringBuilder strSql = new StringBuilder();
        //        strSql.Append(@"   select ItemNumber,ItemNumber,ItemName,ClauseText from LawProvClauseItem where provinId  = '" + provInId + "' and ClauseNumber='" + ClauseNumber + "' and ItemNumber is not null ");
        //        Database oBase = DatabaseFactory.CreateDatabase("lawwork");
        //        DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
        //        DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
        //        if (OtB.Rows.Count > 0)
        //        {
        //            return OtB;
        //        }
        //        else
        //        {
        //            return new DataTable();
        //        }
        //        //else
        //        //{
        //        //    StringBuilder strSql1 = new StringBuilder();
        //        //    strSql1.Append(@"   select ItemNumber,ItemNumber,ItemName,ClauseText from LawProvClauseItem where ClauseInID='" + clauseInId + "'and ItemNumber is not null ");
        //        //    Database oBase1 = DatabaseFactory.CreateDatabase("lawwork");
        //        //    DbCommand oCmd1 = oBase1.GetSqlStringCommand(strSql1.ToString());
        //        //    DataTable OtB1 = oBase1.ExecuteDataSet(oCmd1).Tables[0];
        //        //    if (OtB1.Rows.Count > 0)
        //        //    {
        //        //        return OtB1;
        //        //    }
        //        //    else
        //        //    {
        //        //        return new DataTable();
        //        //    }
        //        //}
        //    }
        //    catch (Exception ex)
        //    {

        //        throw new Exception(ex.Message);
        //    }


        //} 
        #endregion

        /// <summary>
        /// 获取法律法规项信息（查询）
        /// </summary>
        /// <param name="CATInID"></param>
        /// <returns></returns>
        public List<T_Law_Article> GetLawItemData(int provInId)
        {
            try
            {
                return SqlManage.sqlServerLawMachine.Select<T_Law_Article>().Where(x => x.LP_ParentID == provInId && x.LP_Type == "92").OrderBy(x => x.LP_Item_Number).ToList();

                #region 原来代码
                //StringBuilder strSql = new StringBuilder();
                //strSql.Append(@"   select ItemNumber,ItemNumber,ItemName,ClauseText from LawProvClauseItem where provinId  = '" + provInId + "'  and ItemNumber is not null ");
                //Database oBase = DatabaseFactory.CreateDatabase("lawwork");
                //DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
                //DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
                //if (OtB.Rows.Count > 0)
                //{
                //    return OtB;
                //}
                //else
                //{
                //    return new DataTable();
                //}
                //else
                //{
                //    StringBuilder strSql1 = new StringBuilder();
                //    strSql1.Append(@"   select ItemNumber,ItemNumber,ItemName,ClauseText from LawProvClauseItem where ClauseInID='" + clauseInId + "'and ItemNumber is not null ");
                //    Database oBase1 = DatabaseFactory.CreateDatabase("lawwork");
                //    DbCommand oCmd1 = oBase1.GetSqlStringCommand(strSql1.ToString());
                //    DataTable OtB1 = oBase1.ExecuteDataSet(oCmd1).Tables[0];
                //    if (OtB1.Rows.Count > 0)
                //    {
                //        return OtB1;
                //    }
                //    else
                //    {
                //        return new DataTable();
                //    }
                //} 
                #endregion
            }
            catch (Exception ex)
            {                
                throw new Exception(ex.Message);
            }


        }

        /// <summary>
        /// 获取法律法规条款项信息（推送）
        /// </summary>
        /// <param name="CATInID"></param>
        /// <returns></returns>
        public DataTable GetLawProvDataByTuiSong(string CATInID, string ccID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@" select distinct DataId from t_Case_QuoteItems where catinid='" + CATInID + "' and CaseId in(select caseid from t_Case_HeadWords_Associate where HeadWordId in('" + ccID + "')) and ItemTypeId = 2 ");
            Database oBase = DatabaseFactory.CreateDatabase("Caselibrary");
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }

        }

        /// <summary>
        /// 获取法律法规条款项界面信息（推送）
        /// </summary>
        /// <param name="CATInID"></param>
        /// <returns></returns>
        public DataTable GetTuiSongData(string catInIDs)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"    	select ProvInID,ProvNumber,ProvText,''as ProvInName   from LawProv
	where LawProv.ProvInID in (" + catInIDs + ") and LawProv.ProvNumber not in(-1,0)  ");
            //if (!string.IsNullOrEmpty(condition))
            //{
            //    strSql.Append(condition);
            //}
            strSql.Append(@" order by LawProv.ProvNumber ");

            Database oBase = DatabaseFactory.CreateDatabase("lawwork");
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }
        }

        public DataTable GetAllByWhere(string where)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append(@" 
 if object_id(N'#tAllss',N'U') is not null
        drop table #tAllss  
         select identity(int,1,1)as showindex ,u.ProvInID ,u.ProvInName   ,u.ProvNumber ,   u.LostFlag1  into #tAllss  from 
         ( select  top 4295817 a.ProvInID as ProvInID  , ' ' as ProvInName , ProvNumber,LostFlag as LostFlag1  from dbo.LawProv as a 
         join dbo.LawCAT as b 
         on a.CATInID=b.CATInID
          where " + where
                 + @"
         order by len(b.CATName),a.ProvInID
          )as u 

          select * from  #tAllss order by showindex   ");
            Database oBase = DatabaseFactory.CreateDatabase("lawwork");
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());


            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }


        }


        /// <summary>
        /// 根据条获取条信息
        /// </summary>
        /// <param name="ProvID"></param>
        /// <returns></returns>
        public string GetProvText(string ProvID)
        {
            //根据条获取条信息 再根据条获取款信息 有可能条下面只有项信息 所以需要在获取不到款信息后直接获取项信息 2023-06-29表述工具引用法规功能切库方法需求
            StringBuilder sb = new StringBuilder();
            var tla = SqlManage.sqlServerLawMachine.Select<T_Law_Article>().Where(x => x.LP_ID == Convert.ToInt32(ProvID)).First();
            if (tla != null)
            {
                AddLawInfo(sb, tla);
                var tlas = SqlManage.sqlServerLawMachine.Select<T_Law_Article>().Where(x => x.LP_ParentID == Convert.ToInt32(ProvID) && x.LP_Type == "91").OrderBy(x => x.LP_Paragraph_Number).ToList();
                if (tlas != null && tlas.Count > 0)
                {
                    tlas.ForEach(a =>
                    {
                        AddLawInfo(sb, a);
                        if (!string.IsNullOrWhiteSpace(a.LP_ID.ToString()))
                        {
                            var tlas1 = SqlManage.sqlServerLawMachine.Select<T_Law_Article>().Where(x => x.LP_ParentID == Convert.ToInt32(a.LP_ID.ToString()) && x.LP_Type == "92").OrderBy(x => x.LP_Item_Number).ToList();
                            tlas1.ForEach(x => AddLawInfo(sb, x));
                        }
                    });
                }
                else
                {
                    tlas = SqlManage.sqlServerLawMachine.Select<T_Law_Article>().Where(x => x.LP_ParentID == Convert.ToInt32(ProvID) && x.LP_Type == "92").OrderBy(x => x.LP_Item_Number).ToList();
                    if (tlas != null && tlas.Count > 0)
                    {
                        tlas.ForEach(a =>
                        {
                            AddLawInfo(sb, a);
                        });
                    }
                }
            }
            return sb.ToString();

            #region 原有代码
            //string sql = $" select LP_Text  from T_Law_Article where LP_ID = '{ProvID}' and LP_Type = 91 ";
            //return SqlManage.ExecuteScalarSql(sql, "LawMachine");


            //StringBuilder strSql = new StringBuilder();
            //strSql.Append(@" select ProvText from dbo.LawProv  where ProvInID=@ProvID ");
            //Database oBase = DatabaseFactory.CreateDatabase("lawwork");
            //DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            //oBase.AddInParameter(oCmd, "@ProvID", DbType.String, ProvID);
            //string provText = LdFormat.ObjectToString(oBase.ExecuteScalar(oCmd));
            //2023年3月6日 孟文点击报错 发现数据结构已变动 无法追踪到需求原始人
            //if (string.IsNullOrEmpty(provText))
            //{
            //    string sql = $" select benchmark from discretion_benchmark  where ID= {ProvID} ";
            //    oCmd = oBase.GetSqlStringCommand(sql);
            //    provText = LdFormat.ObjectToString(oBase.ExecuteScalar(oCmd));
            //}
            // return provText; 
            #endregion
        }

        #region 旧有代码
        /// <summary>
        /// 根据条获取条名称
        /// </summary>
        /// <param name="ProvID"></param>
        /// <returns></returns>
        //public string GetArticleName(string ProvID)
        //{
        //    StringBuilder strSql = new StringBuilder();
        //    strSql.Append(@" select ArticleName from dbo.LawProv  where ProvInID=@ProvID ");
        //    Database oBase = DatabaseFactory.CreateDatabase("lawwork");
        //    DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
        //    oBase.AddInParameter(oCmd, "@ProvID", DbType.String, ProvID);
        //    string provText = LdFormat.ObjectToString(oBase.ExecuteScalar(oCmd));
        //    return provText;
        //}

        ///// <summary>
        ///// 根据条和款获取款信息
        ///// </summary>
        ///// <param name="ProvID"></param>
        ///// <returns></returns>
        //public string GetClauseText(string ProvID, string clauseNumber)
        //{
        //    StringBuilder strSql = new StringBuilder();
        //    strSql.Append(@"select ClauseText from  dbo.LawProvClauseItem where ProvInID='" + ProvID + "'and ClauseInID='" + clauseNumber + "' ");
        //    Database oBase = DatabaseFactory.CreateDatabase("lawwork");
        //    DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
        //    return LdFormat.ObjectToString(oBase.ExecuteScalar(oCmd));
        //} 
        #endregion


        /// <summary>
        /// 拼接法条内容和信息
        /// </summary>
        private void AddLawInfo(StringBuilder sb, T_Law_Article article)
        {
            //if (!string.IsNullOrWhiteSpace(article.LP_ArticleName))
            //    sb.AppendLine(article.LP_ArticleName);
            if (!string.IsNullOrWhiteSpace(article.LP_Name) || !string.IsNullOrWhiteSpace(article.LP_Text))
                sb.AppendLine(article.LP_Name + article.LP_Text);
        }

        /// <summary>
        /// 根据条和款获取款信息
        /// </summary>
        /// <param name="ProvID"></param>
        /// <returns></returns>
        public string GetClauseTextShow(string ProvID, string clauseNumber)
        {
            StringBuilder sb = new StringBuilder();
            var tla = SqlManage.sqlServerLawMachine.Select<T_Law_Article>().Where(x => x.LP_ID == Convert.ToInt32(ProvID)).First();
            if (tla != null)
            {
                AddLawInfo(sb, tla);
                tla = SqlManage.sqlServerLawMachine.Select<T_Law_Article>().Where(x => x.LP_ParentID == Convert.ToInt32(ProvID) && x.LP_Type == "91" && x.LP_Paragraph_Number == Convert.ToInt32(clauseNumber)).First();
                if (tla != null)
                {
                    AddLawInfo(sb, tla);
                    if (!string.IsNullOrWhiteSpace(tla.LP_ID.ToString()))
                    {
                        var tlas = SqlManage.sqlServerLawMachine.Select<T_Law_Article>().Where(x => x.LP_ParentID == Convert.ToInt32(tla.LP_ID.ToString()) && x.LP_Type == "92").OrderBy(x => x.LP_Item_Number).ToList();
                        if (tlas != null && tlas.Count > 0)
                        {
                            tlas.ForEach(x => AddLawInfo(sb, x));
                        }
                    }
                }
            }
            return sb.ToString();

            #region 原来代码
            //StringBuilder strSql = new StringBuilder();
            //strSql.Append(@"select  ClauseText,* from LawProvClauseItem where provinId  ='" + ProvID + "' and ClauseNumber='" + clauseNumber + "' and ItemNumber is null  ");
            //Database oBase = DatabaseFactory.CreateDatabase("lawwork");
            //DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            //return LdFormat.ObjectToString(oBase.ExecuteScalar(oCmd)); 
            #endregion
        }

        /// <summary>
        /// 根据条、款、项获取项信息
        /// </summary>
        /// <param name="ProvID"></param>
        /// <returns></returns>
        public string GetItemShow(string ProvID, string clauseNumber, string itemNumber)
        {
            StringBuilder sb = new StringBuilder();
            var tla = SqlManage.sqlServerLawMachine.Select<T_Law_Article>().Where(x => x.LP_ID == Convert.ToInt32(ProvID)).First();
            if (tla != null)
            {
                AddLawInfo(sb, tla);
                tla = SqlManage.sqlServerLawMachine.Select<T_Law_Article>().Where(x => x.LP_ParentID == Convert.ToInt32(ProvID) && x.LP_Type == "91" && x.LP_Paragraph_Number == Convert.ToInt32(clauseNumber)).First();
                if (tla != null)
                {
                    AddLawInfo(sb, tla);
                    if (!string.IsNullOrWhiteSpace(tla.LP_ID.ToString()))
                    {
                        tla = SqlManage.sqlServerLawMachine.Select<T_Law_Article>().Where(x => x.LP_ParentID == Convert.ToInt32(tla.LP_ID.ToString()) && x.LP_Type == "92" && x.LP_Item_Number == Convert.ToInt32(itemNumber)).First();
                        if (tla != null)
                            AddLawInfo(sb, tla);
                    }
                }
                else
                {
                    tla = SqlManage.sqlServerLawMachine.Select<T_Law_Article>().Where(x => x.LP_ParentID == Convert.ToInt32(ProvID) && x.LP_Type == "92" && x.LP_Item_Number == Convert.ToInt32(itemNumber)).First();
                    if (tla != null)
                        AddLawInfo(sb, tla);
                }
            }
            return sb.ToString();

            #region 原来代码
            //StringBuilder strSql = new StringBuilder();
            //strSql.Append(@"select ClauseText  from dbo.LawProvClauseItem where provinId='" + ProvID + "'and ClauseNumber='" + clauseNumber + "'and ItemNumber='" + itemNumber + "' ");
            //Database oBase = DatabaseFactory.CreateDatabase("lawwork");
            //DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            //return LdFormat.ObjectToString(oBase.ExecuteScalar(oCmd)); 
            #endregion
        }

        /// <summary>
        /// 根据条、款、项获取项信息
        /// </summary>
        /// <param name="ProvID"></param>
        /// <returns></returns>
        //public string GetItemText(string ProvID, string clauseNumber, string itemNumber)
        //{
        //    StringBuilder strSql = new StringBuilder();
        //    strSql.Append(@"select ClauseText  from dbo.LawProvClauseItem where ProvInID='" + ProvID + "'and ClauseInID='" + clauseNumber + "'and ItemNumber='" + itemNumber + "' ");
        //    Database oBase = DatabaseFactory.CreateDatabase("lawwork");
        //    DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
        //    return LdFormat.ObjectToString(oBase.ExecuteScalar(oCmd));
        //}

        /// <summary>
        /// 根据条、款、项、xmlPath获取是否已存在记录
        /// </summary>
        /// <param name="ProvID"></param>
        /// <returns></returns>
        public bool IsExistsP(string ccName, string ccID, string provID, string clauseNumber, string itemNumber, string caseType, params string[] xmlPath)
        {
            StringBuilder strSql = new StringBuilder();
            if (ccName == "刑事" || ccName == "行政执法")
            {
                strSql.Append(@" select count(1) from SYS_QuoteLawRel 
where OLR_SituationID = '" + ccID + "' and OLR_Law_ProvID = '" + provID + "' and   OLR_Law_ClauseNumber = '" + clauseNumber + "' and OLR_Law_ItemNumber = '" + itemNumber + "' ");
            }
            else if (ccName == "民事")
            {
                strSql.Append(@" select count(1) from SYS_QuoteLawRel inner join SYS_Label_LabelRel on OLR_SituationID=LLR_LawLabelID  
where LLR_FactLabelXpath= '" + xmlPath[0] + "' and  OLR_CaseCauseID = '" + ccID + "' and OLR_Law_ProvID = '" + provID + "' and  OLR_Law_ClauseNumber = '" + clauseNumber + "' and OLR_Law_ItemNumber = '" + itemNumber + "' ");
            }
            else if (ccName == "程序法")
            {
                strSql.Append(@" select count(1) from SYS_QuoteLawRel inner join SYS_QuoteLawCondition on SYS_QuoteLawRel.OLR_SituationID=QLC_ID 
where QLC_CaseType='" + caseType + "'and OLR_Law_ProvID = '" + provID + "' and  OLR_Law_ClauseNumber = '" + clauseNumber + "' and OLR_Law_ItemNumber = '" + itemNumber + "' ");
            }
            else if (ccName == "组合")
            {
                strSql.Append(@" select count(1) from SYS_QuoteLawRel inner join SYS_QuoteLawCondition on SYS_QuoteLawRel.OLR_SituationID=QLC_ID 
where QLC_CaseCauseId='" + caseType + "'and OLR_Law_ProvID = '" + provID + "' and  OLR_Law_ClauseNumber = '" + clauseNumber + "' and OLR_Law_ItemNumber = '" + itemNumber + "' ");
            }
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            return Convert.ToInt32(oBase.ExecuteScalar(oCmd)) > 0 ? true : false;
        }

        /// <summary>
        /// 根据条获取是否已存在记录
        /// </summary>
        /// <param name="ProvID"></param>
        /// <returns></returns>
        public bool IsExistsProv(string ccName, string ccID, string ProvID)
        {
            StringBuilder strSql = new StringBuilder();
            if (ccName == "刑事" || ccName == "行政执法")
            {
                strSql.Append(@" select count(1) from SYS_QuoteLawRel where OLR_SituationID = '" + ccID + "' and OLR_Law_ProvID = '" + ProvID + "' ");
            }
            //else if (ccName == "民事")
            //{
            //    strSql.Append(@" select count(1) from SYS_QuoteLawRel inner join SYS_Label_LabelRel on OLR_SituationID=LLR_LawLabelID  where LLR_FactLabelXpath= '" + xmlPath[0] + "' and  OLR_CaseCauseID = '" + ccID + "' and OLR_Law_ProvID = '" + ProvID + "' ");

            //}
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            return Convert.ToInt32(oBase.ExecuteScalar(oCmd)) > 0 ? true : false;
        }

        /// <summary>
        /// 获取主题词关联法条数据
        /// </summary>
        /// <param name="headWordID"></param>
        /// <returns></returns>
        public DataTable GetData(string headWordID)
        {

            string sql = $@"   select  a.HW_ID as HeadwordInID,b.HW_Name as HeadwordN,T_Law.L_ID as CATInID,  T_Law.L_Name as CATName, T_Law.L_Standardcatname as  Standardcatname,
                T_Law.L_DateIssued as PromuDate,
               T_Law.EffectiveStatus_ID as LostFlag,T_Law.EffectiveGrade_ID as HWType,SYS_EffectiveGrade.E_Name as HWName,
               a.LP_ID as ProvInID,T_Law_Article.LP_ArticleName as articleName,T_Law_Article.LP_Text as provText ,T_Law_Article.LP_Article_Number as ProvNumber,
               case T_Law.EffectiveStatus_ID when '1' then 1 when '0' then 0 else 2 end as lawFlag,
               T_Law.L_EffectiveDate as EffectiveDate,SYS_EffectiveGrade.E_Code as HCode,T_Law.IsBasicLaw,T_Law_Article.BasisType,
               '' as clauseNum,'' as clauseName,'' as ItemNumber,'' as ItemName,PH_ID ProvHwordInID
                 from T_Law_Article_HeadwordRel a
                left join T_Headword b on a.HW_ID=b.HW_ID
                left join T_Law as T_Law on a.L_ID=T_Law.L_ID
               left join T_Law_Article as T_Law_Article on a.LP_ID=T_Law_Article.LP_ID
               left join SYS_EffectiveGrade as SYS_EffectiveGrade on SYS_EffectiveGrade.E_ID=T_Law.EffectiveGrade_ID
                where a.HW_ID in({headWordID}) ";
            DataTable dt = SqlManage.sqlServerLawMachine.Select<object>().WithSql(sql).ToDataTable();
            return dt ?? new DataTable();

            #region 原有代码
            //            string sql = $@" select a.HeadwordInID,b.HeadwordN,d.CATInID,d.CATName,d.Standardcatname,d.PromuDate,d.LostFlag,d.HWType,e.HWName,  a.ProvInID,c.articleName,c.provText,c.ProvNumber,
            //    case d.STFlag when '1' then 1 when '0' then 0 else 2 end as lawFlag ,d.EffectiveDate,e.HCode, f.IsBasicLaw,g.BasisType,'' as clauseNum,'' as clauseName,'' as ItemNumber,'' as ItemName,a.ProvHwordInID
            //    from ProvHeadword a left join Headword b on a.HeadwordInID = b.HeadwordInID
            // left join LawProv c on c.ProvInID = a.ProvInID
            // left join LawCAT d on c.CATInID = d.CATInID
            //  left join HeadWindows e  on e.HWType = d.HWType
            //   left join  BasicLawRel f on f.CATInID = d.CATInID 
            //left join LawProvBasisType g on g.ProvInID=c.ProvInID
            //where a.HeadwordInID in({headWordID})   ";
            //            Database oBase = DatabaseFactory.CreateDatabase("lawwork");
            //            DbCommand oCmd = oBase.GetSqlStringCommand(sql);
            //            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            //            if (OtB.Rows.Count > 0)
            //            {
            //                return OtB;
            //            }
            //            else
            //            {
            //                return new DataTable();
            //            } 
            #endregion
        }

    }
}
